package com.qmerak.common;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

/**
 * @author shaohong.cheng
 * @date 2013-5-14
 */
public class DataBaseUtil {
	/**
	 * 
	 * @param sql
	 * @return
	 */
	public static List<Hashtable<String, Object>> query(String sql) {
		List<Hashtable<String, Object>> result = new LinkedList<Hashtable<String, Object>>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = SpringUtil.getDataSource().getConnection();
			statement = connection.createStatement();
			ResultSet set = statement.executeQuery(sql);
			result = resultToList(set);
			System.out.println("查询结束!!!" + result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	/**
	 * 将数据集合转换为列表
	 * 
	 * @param set
	 * @return
	 */
	private static List<Hashtable<String, Object>> resultToList(ResultSet set) {
		List<Hashtable<String, Object>> list = new LinkedList<Hashtable<String, Object>>();
		try {
			ResultSetMetaData data = set.getMetaData();
			while (set.next()) {
				Hashtable<String, Object> table = new Hashtable<String, Object>();
				for (int i = 1; i <= data.getColumnCount(); i++) {
					String name = data.getColumnName(i);
					Object object = set.getObject(name);
					if (object != null) {
						table.put(name, object);
					}
				}
				list.add(table);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return list;
	}

	/**
	 * 
	 * @param objects
	 * @return
	 */
	public static String sum(Object... objects) {
		return String.valueOf(add(objects));
	}

	/**
	 * 
	 * @param objects
	 * @return
	 */
	public static int add(Object... objects) {
		int ret = 0;
		for (Object object : objects) {
			if (object == null || "".equals(object)) {
				continue;
			}
			ret += Integer.valueOf(String.valueOf(object));
		}
		return ret;
	}

	/**
	 * 
	 * @param date
	 * @param format
	 * @return
	 */
	public static int calAge(String date, String format) {
		return BSCalendarHelper.getDispersionOfYears(BSCalendar.now()
				.toString(), new BSCalendar(date, format).toString());
	}

	public static String ageToDate(int age, String format) {
		return BSCalendar.now().addYear(-age).toString(format);
	}

	public static void main(String[] args) {
		System.out.println(add("1", "", "3"));
		System.out.println(ageToDate(30, "yyyy-mm-dd"));
	}

	public static Map<String, String> calRate(Map<String, Object> map) {
		Map<String, String> result = new HashMap<String, String>();
		String sumN = "0";
		for (Entry<String, Object> entry : map.entrySet()) {
			sumN = sum(sumN, entry.getValue());
		}
		double otherN = 0.0;
		for (Entry<String, Object> entry : map.entrySet()) {
			double rate = 0.0;
			if (null != entry.getValue() && !"".equals(entry.getValue())) {
				rate = Double.parseDouble(String.valueOf(entry.getValue()))
						/ Double.parseDouble(sumN) * 100;
				rate = new BigDecimal(rate).setScale(2,
						BigDecimal.ROUND_HALF_UP).doubleValue();
				if ("null".equals(entry.getKey()) || "".equals(entry.getKey())) {
					otherN += rate;
				} else {
					result.put(entry.getKey(), String.valueOf(rate));
				}
			}
		}
		if (otherN != 0) {
			result.put("other", String.valueOf(otherN));
		}
		return result;
	}
}
